package com.xinan.xs;

import com.jfinal.ext.plugin.tablebind.TableBind;
import com.jfinal.kit.StringKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.xinan.weixin.resource.WxResourceController;

import java.sql.Timestamp;
import java.util.List;

@TableBind(tableName = "hygl_xf")
public class HyglXf extends Model<HyglXf> {
    public static final HyglXf dao = new HyglXf();

    //columns START
    private String ID = "id";  //
    private String CARD = "card";  //卡号
    private String XFBZ = "xfbz";  //标志
    private String XFMC = "xfmc";  //消费名称
    private String XFSL = "xfsl";  //消费数量
    private String XFJE = "xfje";  //消费金额
    private String CZYMC = "czymc";  //操作人员名称
    private String XFTC = "xftc";  //消费提成
    private String XFZK = "xfzk";  //消费折扣
    private String XFSJJG = "xfsjjg";  //消费实际金额
    private String LRRYDM = "lrrydm";  //录入人代码
    private String LRRQ = "lrrq";  //录入日期
    private String YXBZ = "yxbz";  //0 未结账

    public void setId(int value) {
        set(ID, value);
    }

    public int getId() {
        return get(ID);
    }

    public void setCard(String value) {
        set(CARD, value);
    }

    public String getCard() {
        return get(CARD);
    }

    public void setXfbz(String value) {
        set(XFBZ, value);
    }

    public String getXfbz() {
        return get(XFBZ);
    }

    public void setXfmc(String value) {
        set(XFMC, value);
    }

    public String getXfmc() {
        return get(XFMC);
    }

    public void setXfsl(String value) {
        set(XFSL, value);
    }

    public String getXfsl() {
        return get(XFSL);
    }

    public void setXfje(String value) {
        set(XFJE, value);
    }

    public String getXfje() {
        return get(XFJE);
    }

    public void setCzymc(String value) {
        set(CZYMC, value);
    }

    public String getCzymc() {
        return get(CZYMC);
    }

    public void setXftc(String value) {
        set(XFTC, value);
    }

    public String getXftc() {
        return get(XFTC);
    }

    public void setXfzk(String value) {
        set(XFZK, value);
    }

    public String getXfzk() {
        return get(XFZK);
    }

    public void setXfsjjg(String value) {
        set(XFSJJG, value);
    }

    public String getXfsjjg() {
        return get(XFSJJG);
    }

    public void setLrrydm(String value) {
        set(LRRYDM, value);
    }

    public String getLrrydm() {
        return get(LRRYDM);
    }

    public void setLrrq(Timestamp value) {
        set(LRRQ, value);
    }

    public Timestamp getLrrq() {
        return get(LRRQ);
    }

    public void setYxbz(String value) {
        set(YXBZ, value);
    }

    public String getYxbz() {
        return get(YXBZ);
    }
    //columns END

    public List<HyglXf> findAll() {
        return find("select * from hygl_xf");
    }

    /**
     * @param where
     * @return Record
     */
    
    public List<Record> findAlls(String where) {
        String sqlExp = " from hygl_xf s";
        if (StringKit.notBlank(where)) {
            sqlExp += " where " + where;
        }
        String order =" order by card,lrrq desc";
        return Db.find("select *,CAST(xfsjjg as DECIMAL(10,2)) as xfsjjgs, (select cnname from auth_user where username=lrrydm) as lrrymc," +
                "(select yhm from hygl a where a.card=s.card) as yhm " + sqlExp);
    }
    
    public List<Record> findByHYinfo(String where){
    	String sqlExp = " from hygl_xf s,hygl h";
    	if(StringKit.notBlank(where)){
    		sqlExp += " where "+ where;
    	}
    	String order =" order by s.card,s.lrrq asc";
    	return Db.find("select s.*,CAST(s.xfsjjg as DECIMAL(10,2)) as xfsjjgs, (select cnname from auth_user where username=lrrydm) as lrrymc," +
                "(select yhm from hygl a where a.card=s.card) as yhm " + sqlExp+order);
    }
    
    /* 会员查询所有记录 */
    public List<Record> findByHYinfo2(String where){
//    	String sqlExp = " from hygl_xf s,hygl h";
    	String sqlExp = "from (select card,lrrq,xfmc,CAST(xfsjjg as DECIMAL(10,2)) as xfsjjgs from hygl_xf where yxbz='1' union select card,czrq as lrrq,'充值' as xfmc,CAST(czje AS DECIMAL(10,2)) as xfsjjgs from hygl_cz union select card,bkrq as lrrq,'办卡' as xfmc,CAST(ysje AS DECIMAL(10,2)) as xfsjjgs from hygl) s,hygl h ";
    	if(StringKit.notBlank(where)){
    		sqlExp += " where "+ where;
    	}
    	
    	String order =" order by s.card,s.lrrq asc";
//    	return Db.find("select s.*,CAST(s.xfsjjg as DECIMAL(10,2)) as xfsjjgs, (select cnname from auth_user where username=lrrydm) as lrrymc," +
//                "(select yhm from hygl a where a.card=s.card) as yhm " + sqlExp+order);
    	return Db.find("select * "+sqlExp+order);
    }
    
    public List<Record> findmoey(String where) {
        String sqlExp = " from hygl_xf s";
        if (StringKit.notBlank(where)) {
            sqlExp += " where " + where;
        }
        return Db.find("select *,sum(CAST(xfsjjg as DECIMAL(10,2))) as xfsjjgs, sum(CAST(xfsl as DECIMAL(10,0))) as xfsls " + sqlExp);
    }
    
    public List<Record> findmoeyByHYinfo(String where){
    	String sqlExp = " from hygl_xf s,hygl h";
    	if(StringKit.notBlank(where)){
    		sqlExp += " where "+ where;
    	}
    	String order =" order by s.card,s.lrrq desc";
    	return Db.find("select s.*,sum(CAST(s.xfsjjg as DECIMAL(10,2))) as xfsjjgs, sum(CAST(s.xfsl as DECIMAL(10,0))) as xfsls " + sqlExp+order);
    }

    public Page<HyglXf> page(int pageNumber, int pageSize, String where) {
        String sqlExp = "from hygl_xf";
        if (StringKit.notBlank(where)) {
            sqlExp += " where " + where;
        }
        String order = " order by  card,yxbz asc,lrrq";
        return paginate(pageNumber, pageSize, "select *", sqlExp + order);
    }
    public Page<HyglXf> pagexstj(int pageNumber, int pageSize, String where) {
        String sqlExp = "from hygl_xf";
        String sql =" from (   select a.* ,  sum(a.xfsl ) as xfsls ,sum(CAST(a.xftc as DECIMAL(10,2))) as xftcs ," +
        		"sum(CAST(a.xfsjjg as DECIMAL(10,2))) as xfsjjgs, sum(CAST(a.xfje as DECIMAL(10,2))) as xfjes," +
                "(select orgid from auth_user where cnname = czymc LIMIT 1) as orgid," +
                "(case when a.xfbz='sptj' then '商品' when a.xfbz='fwtj'  then '服务'    end) as xfbzs from hygl_xf  a group by a.xfmc,a.xfbz,a.card ) b";
        if (StringKit.notBlank(where)) {
        	sql += " where " + where;
        }
        String order = " order by  xfbz desc,xfmc,lrrq";
        return paginate(pageNumber, pageSize, "select * ", sql + order);
    }

    public Page<HyglXf> pagexstctj(int pageNumber, int pageSize, String where) {
        String sqlExp = "from hygl_xf";
        String sql =" from (   select a.* ,  sum(a.xfsl ) as xfsls ,sum(CAST(a.xftc as DECIMAL(10,2))) as xftcs ," +
        		"sum(CAST(a.xfsjjg as DECIMAL(10,2))) as xfsjjgs, sum(CAST(a.xfje as DECIMAL(10,2))) as xfjes," +
                "(select orgid from auth_user where cnname = czymc LIMIT 1) as orgid," +
                "(case when a.xfbz='sptj' then '商品' when a.xfbz='fwtj'  then '服务'    end) as xfbzs from hygl_xf  a group by a.czymc,a.xfbz,a.xfmc ) b";
        if (StringKit.notBlank(where)) {
        	sql += " where " + where;
        }
        String order = " order by  lrrq";
        return paginate(pageNumber, pageSize, "select * ", sql + order);
    }

    
    public List<Record> findxstj(String where) {
        String sqlExp = " from hygl_xf s";
        if (StringKit.notBlank(where)) {
            sqlExp += " where " + where;
        }
        String order =" order by xfbz desc,xfmc,lrrq";
        return Db.find("select *,CAST(xfsjjg as DECIMAL(10,2)) as xfsjjgs, " +
                "CAST(xfsjjg as DECIMAL(10,2))*CAST(xftc as DECIMAL(10,2)) as xftcje ," +
                "(select cnname from auth_user where username=lrrydm) as lrrymc," +
                "(select yhm from hygl a where a.card=s.card) as yhm " + sqlExp);
    }
}

